<?php

class Damages_Methods_Request_GenerateReport {

  const TRANSPORT_TYPE_PATTERN = '%s - %s';
  const SUM_COLUMN_PATTERN = '%s - razem';
  const WAREHOUSE_NAME = 'Szkody magazynowe';
  const MIN_TIME = 'Min czas';
  const MAX_TIME = 'Max czas';
  const AVG_TIME = 'Średni czas ';

  /**
   *
   * @var string 
   */
  public $type;

  /**
   *
   * @var string 
   */
  public $from;

  /**
   *
   * @var string 
   */
  public $to;
  private $brands = array();
  private $columns = array();
  private $dateColumns = array();
  private $endRange = 'A';

  public function __construct($params = null) {
    $this->type = $params->type;
    $this->from = $params->from;
    $this->to = $params->to;
  }

  public function process() {
    if (!Damages_Auth::getInstance()->checkPermission(array(Damages_Auth::PERMISSION_ADMIN, Damages_Auth::PERMISSION_LS)))
      throw new Damages_Exception_App(Damages_Codes::ACCESS_DENIED);
    require_once 'PHPExcel.php';
//    if ($this->type == 'report_11')
//      return $this->processTimeReport();
    if ($this->type == 'report_12')
      return $this->processTotalReport();
    $objPHPExcel = new PHPExcel();
    $struct = $this->getOutputStruct();

    $title = $this->getTitle();

    $q = DamagesReportsViewQuery::create();
    if ($this->from)
      $q->filterByCarDamageDate($this->from, Criteria::GREATER_EQUAL);
    if ($this->to)
      $q->filterByCarDamageDate($this->to, Criteria::LESS_EQUAL);
    $q->groupByBrandId();
    $q->addGroupByColumn(DamagesReportsViewPeer::PROCESS_TYPE_ID);
    $q->addGroupByColumn(DamagesReportsViewPeer::TRANSPORT_TYPE);
    $q->withColumn(sprintf('MAX(%s)', DamagesReportsViewPeer::BRAND_NAME), 'brand_name');
    $q->withColumn(sprintf('MAX(%s)', DamagesReportsViewPeer::PROCESS_TYPE_NAME), 'process_type_name');
    $q->withColumn(sprintf('MAX(%s)', DamagesReportsViewPeer::PROCESS_TYPE_CODE), 'process_type_code');
    $q->withColumn(DamagesReportsViewPeer::TRANSPORT_TYPE, 'transport_type');
    switch ($this->type) {
      case 'report_1':
        $q->filterByStatusCode('s9', Criteria::NOT_EQUAL);
        $q->withColumn(sprintf('COUNT(DISTINCT %s)', DamagesReportsViewPeer::CAR_DAMAGE_ID), 'value');
        break;
      case 'report_2':
        $q->withColumn(sprintf('SUM(%s)', DamagesReportsViewPeer::ESTIMATED_BUDGET_VALUE), 'value');
        break;
      case 'report_3':
        $q->withColumn(sprintf('SUM(%s)', DamagesReportsViewPeer::ESTIMATED_BUDGET_DISCOUNT_FLAG), 'value');
        break;
      case 'report_4':
// TODO: upusty
        $q->withColumn(sprintf('SUM(%s)', DamagesReportsViewPeer::ESTIMATED_BUDGET_DISCOUNT_VALUE), 'value');
        break;
      case 'report_5':
        $q->withColumn(sprintf('COUNT(DISTINCT %s)', DamagesReportsViewPeer::INVOICE_ID), 'value');
        break;
      case 'report_6':
        $q->withColumn(sprintf('SUM(%s)', DamagesReportsViewPeer::INVOICE_VALUE), 'value');
        break;
      case 'report_7':
        $q->filterByNoteTypeId(1);
        $q->withColumn(sprintf('COUNT(DISTINCT %s)', DamagesReportsViewPeer::NOTE_ID), 'value');
        break;
      case 'report_8':
        $q->filterByNoteTypeId(1);
        $q->withColumn(sprintf('SUM(%s)', DamagesReportsViewPeer::NOTE_VALUE), 'value');
        break;
      case 'report_9':
        $q->filterByNoteTypeId(2);
        $q->withColumn(sprintf('COUNT(DISTINCT %s)', DamagesReportsViewPeer::NOTE_ID), 'value');
        break;
      case 'report_10':
        $q->filterByNoteTypeId(2);
        $q->withColumn(sprintf('SUM(%s)', DamagesReportsViewPeer::NOTE_VALUE), 'value');
        break;
      default:
        $q->withColumn('0', 'value');
        break;
    }
    $this->renderHeadRow($objPHPExcel);
    $this->renderBrands($objPHPExcel);
    $this->renderTitle($objPHPExcel, $title);
    $q->select(array(
        'brand_name',
        'process_type_name',
        'transport_type',
        'value'
    ));

    $result = $q->find();
    $sum = array();
    foreach ($this->columns as $column) {
      $sum[$column] = 0;
    }
    foreach ($result->toArray() as $value) {
      $val = $value['value'] ? (float) $value['value'] : 0;
      switch ($value['process_type_code']) {
        case 'sm1':
          $struct = $this->setStructValue($struct, $value['brand_name'], sprintf(self::TRANSPORT_TYPE_PATTERN, $value['process_type_name'], $value['transport_type']), $val);
          $sum = $this->setSumValue($sum, sprintf(self::TRANSPORT_TYPE_PATTERN, $value['process_type_name'], $value['transport_type']), $val);
          $struct = $this->setStructValue($struct, $value['brand_name'], sprintf(self::SUM_COLUMN_PATTERN, $value['process_type_name']), $val);
          $sum = $this->setSumValue($sum, sprintf(self::SUM_COLUMN_PATTERN, $value['process_type_name']), $val);
          break;
        case 'smg1':
        case 'smg2':
          $struct = $this->setStructValue($struct, $value['brand_name'], $value['process_type_name'], $val);
          $sum = $this->setSumValue($sum, $value['process_type_name'], $val);
          $struct = $this->setStructValue($struct, $value['brand_name'], sprintf(self::SUM_COLUMN_PATTERN, self::WAREHOUSE_NAME), $val);
          $sum = $this->setSumValue($sum, sprintf(self::SUM_COLUMN_PATTERN, self::WAREHOUSE_NAME), $val);
          break;
        default:
          $struct = $this->setStructValue($struct, $value['brand_name'], $value['process_type_name'], $val);
          $sum = $this->setSumValue($sum, $value['process_type_name'], $val);
          break;
      }
    }

    $struct['Total'] = $sum;
    $objPHPExcel->setActiveSheetIndex()->fromArray($struct, null, 'B3', true);
    $fileName = sprintf('%s.xlsx', $title);
    $objPHPExcel->getActiveSheet()->setAutoFilter(sprintf('A2:%s2', $this->endRange));
    foreach (range('A', $this->endRange) as $columnID) {
      $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
              ->setAutoSize(true);
    }
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $fileName . '"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
  }

  public function processTimeReport() {
    $objPHPExcel = new PHPExcel();
    $struct = $this->getOutputStruct($this->type);
    $title = $this->getTitle();

    $q = DamagesTimeReportsViewQuery::create();
    if ($this->from)
      $q->filterByCarDamageDate($this->from, Criteria::GREATER_EQUAL);
    if ($this->to)
      $q->filterByCarDamageDate($this->to, Criteria::LESS_EQUAL);
    $q->groupByBrandId();

    $this->renderHeadRow($objPHPExcel);
    $this->renderBrands($objPHPExcel, $this->type);
    $this->renderTitle($objPHPExcel, $title);
    $dateDiffQ = sprintf('Datediff(dd, %1$s, %2$s) 
                   + CASE WHEN Datepart(dw, %1$s) = 7 THEN 1 ELSE 0 END 
                   - (Datediff(wk, %1$s, %2$s) * 2 ) 
                   - CASE WHEN Datepart(dw, %1$s) = 1 THEN 1 ELSE 0 END + 
                   - CASE WHEN Datepart(dw, %2$s) = 1 THEN 1 ELSE 0 
                   END  ', DamagesTimeReportsViewPeer::INVOICE_DATE, DamagesTimeReportsViewPeer::NOTE_DATE);
    $q->withColumn(sprintf('MAX(%s)', DamagesTimeReportsViewPeer::BRAND_NAME), 'brand_name');
    $q->withColumn(sprintf('MIN(%s)', $dateDiffQ), 'min_time');
    $q->withColumn(sprintf('MAX(%s)', $dateDiffQ), 'max_time');
    $q->withColumn(sprintf('AVG(%s)', $dateDiffQ), 'avg_time');
    $q->select(array(
        'brand_name',
        'min_time',
        'max_time',
        'avg_time'
    ));

    $result = $q->find();

    foreach ($result->toArray() as $value) {
      $struct = $this->setStructValue($struct, $value['brand_name'], self::MIN_TIME, $value['min_time']);
      $struct = $this->setStructValue($struct, $value['brand_name'], self::AVG_TIME, $value['avg_time']);
      $struct = $this->setStructValue($struct, $value['brand_name'], self::MAX_TIME, $value['max_time']);
    }

    $objPHPExcel->setActiveSheetIndex()->fromArray($struct, null, 'B3', true);
    $fileName = sprintf('%s.xlsx', $title);
    $objPHPExcel->getActiveSheet()->setAutoFilter(sprintf('A2:%s2', $this->endRange));
    foreach (range('A', $this->endRange) as $columnID) {
      $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
              ->setAutoSize(true);
    }
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $fileName . '"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
  }

  public function processTotalReport() {
    $objPHPExcel = new PHPExcel();
    $title = $this->getTitle();
    $q = DamagesReportsTotalQuery::create();
    if ($this->from)
      $q->filterByDataUszkodzenia($this->from, Criteria::GREATER_EQUAL);
    if ($this->to)
      $q->filterByDataUszkodzenia($this->to, Criteria::LESS_EQUAL);

//    $this->renderBrands($objPHPExcel, $this->type);


    $result = $q->find();
    $arrResult = $result->toArray();
    if ($arrResult) {
      $headerRow = array_keys($arrResult[0]);
    } else {
      $headerRow = DamagesReportsTotalPeer::getFieldNames();
    }
    $this->renderHeadRow($objPHPExcel, $this->type, array_keys($arrResult[0]));
    $this->renderTitle($objPHPExcel, $title);

    $objPHPExcel->setActiveSheetIndex()->fromArray($arrResult, null, 'A3', true);
    $startRow = 3;
    $endRow = $startRow + count($arrResult);
    $this->organizeDateColumns($objPHPExcel, $startRow, $endRow);

    $fileName = sprintf('%s.xlsx', $title);
    $objPHPExcel->getActiveSheet()->setAutoFilter(sprintf('A2:%s2', $this->endRange));

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $fileName . '"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
  }

  private function organizeDateColumns(PHPExcel $objPHPExcel, $startRow, $endRow) {
    foreach ($this->dateColumns as $columnChar) {
      for ($index = $startRow; $index < $endRow; $index++) {
        $val = $objPHPExcel->getActiveSheet()->getCell(sprintf('%s%s', $columnChar, $index))->getValue();
        if ($val) {
          $time = strtotime($val);
          $objPHPExcel->getActiveSheet()->getCell(sprintf('%s%s', $columnChar, $index))->setValue(PHPExcel_Shared_Date::PHPToExcel($time, true, date_default_timezone_get()));
        }
      }
      $objPHPExcel->getActiveSheet()
              ->getStyle(sprintf('%s%s:%s%s', $columnChar, $startRow, $columnChar, $endRow))
              ->getNumberFormat()
              ->setFormatCode('yyyy-mm-dd hh:mm:ss');
    }
  }

  private function setSumValue($sum, $columnName, $value) {
    $sum[$columnName] = isset($sum[$columnName]) ? $sum[$columnName] + $value : $value;
    return $sum;
  }

  private function setStructValue($struct, $brandName, $columnName, $value) {
    if ($struct[$brandName][$columnName]) {
      $struct[$brandName][$columnName] += $value ? $value : 0;
    } else {
      $struct[$brandName][$columnName] = $value ? $value : 0;
    }
    return $struct;
  }

  private function getOutputStruct($type = '') {
    $brands = DamagesDictBrandsQuery::create()->select(array(DamagesDictBrandsPeer::NAME))->find()->toArray();
    $result = array();
    switch ($type) {
      case 'report_11':
        $columns = array(
            self::MIN_TIME,
            self::AVG_TIME,
            self::MAX_TIME,
        );
        break;
      default:
        $processTypes = DamagesProcessTypesQuery::create()->find();
        $transportTypes = DamagesDictPerpetratorsQuery::create()->select(array(DamagesDictPerpetratorsPeer::TRANSPORT_TYPE))->distinct()->find()->toArray();
        $columns = array();
        $internationalSum = 0;
        $warehouseSum = 0;
        foreach ($processTypes as $processType) {
          switch ($processType->getCode()) {
            case 'sm1':
              foreach ($transportTypes as $transportType) {
                $columns[] = sprintf(self::TRANSPORT_TYPE_PATTERN, $processType->getName(), $transportType);
                $internationalSum++;
                if ($internationalSum === 2)
                  $columns[] = sprintf(self::SUM_COLUMN_PATTERN, $processType->getName());
              }
              break;
            case 'smg1':
              $columns[] = $processType->getName();
              $warehouseSum++;
              if ($warehouseSum === 2)
                $columns[] = sprintf(self::SUM_COLUMN_PATTERN, self::WAREHOUSE_NAME);
              break;
            case 'smg2':
              $columns[] = $processType->getName();
              $warehouseSum++;
              if ($warehouseSum === 2)
                $columns[] = sprintf(self::SUM_COLUMN_PATTERN, self::WAREHOUSE_NAME);
              break;
            default:
              $columns[] = $processType->getName();
              break;
          }
        }
        break;
    }
    $this->columns = $columns;
    $this->brands = $brands;
    foreach ($brands as $brand) {
      if (!isset($result[$brand]))
        $result[$brand] = array();
      foreach ($columns as $column) {
        $result[$brand][$column] = 0;
      }
    }

    return $result;
  }

  private function renderTitle(PHPExcel $objPHPExcel, $title) {
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKBLUE);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $title);
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells(sprintf('A1:%s1', $this->endRange));
  }

  private function renderHeadRow(PHPExcel $objPHPExcel, $type = '', $data = null) {
    switch ($type) {
      case 'report_12':
        $columns = $data;
        $cellChar = 'A';
        break;
      default:
        $columns = $this->columns;
        $cellChar = 'B';
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', 'Marka');
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKBLUE);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        break;
    }
    foreach ($columns as $column) {
      if (strstr(strtolower($column), 'data')) {
        $this->dateColumns[] = $cellChar;
      }
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue(sprintf('%s2', $cellChar), $column);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('%s2', $cellChar))->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('%s2', $cellChar))->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('%s2', $cellChar))->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('%s2', $cellChar))->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('%s2', $cellChar))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('%s2', $cellChar))->getFill()->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKBLUE);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('%s2', $cellChar))->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
      $this->endRange = $cellChar;
      $cellChar++;
    }
  }

  private function renderBrands(PHPExcel $objPHPExcel, $type = '') {
    $cellNum = 3;
    foreach ($this->brands as $brand) {
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue(sprintf('A%s', $cellNum), $brand);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('A%s', $cellNum))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('A%s', $cellNum))->getFill()->getStartColor()->setRGB('5081EB');
      $objPHPExcel->getActiveSheet()->getStyle(sprintf('A%s', $cellNum))->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
      $cellNum++;
    }
    switch ($type) {
      case 'report_11':
        break;
      default:
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue(sprintf('A%s', $cellNum), 'Total');
        $objPHPExcel->getActiveSheet()->getStyle(sprintf('A%s', $cellNum))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle(sprintf('A%s', $cellNum))->getFill()->getStartColor()->setRGB('5081EB');
        $objPHPExcel->getActiveSheet()->getStyle(sprintf('A%s', $cellNum))->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
        break;
    }
  }

  private function getTitle() {
    $title = '';
    switch ($this->type) {
      case 'report_1':
        $title = 'Ilość';
        break;
      case 'report_2':
        $title = 'Wartość kosztorysów';
        break;
      case 'report_3':
        $title = 'Ilość upustów';
        break;
      case 'report_4':
        $title = 'Wartość upustów';
        break;
      case 'report_5':
        $title = 'Ilość faktur';
        break;
      case 'report_6':
        $title = 'Wartość faktur';
        break;
      case 'report_7':
        $title = 'Ilość regresów (za szkodę)';
        break;
      case 'report_8':
        $title = 'Wartość regresów (za szkodę)';
        break;
      case 'report_9':
        $title = 'Ilość regresów (za utratę wartości)';
        break;
      case 'report_10':
        $title = 'Wartość regresów (za utratę wartości)';
        break;
      case 'report_11':
        $title = 'Raport czasowy (data regresu - data faktury) - bez weekendów';
        break;
      case 'report_12':
        $title = 'Raport zbiorczy';
        break;
      default:
        $title = 'Niezdefiniowany raport';
        break;
    }
    if ($this->from || $this->to)
      $title = sprintf('%s - ', $title);
    if ($this->from)
      $title = sprintf('%sod %s ', $title, $this->from);
    if ($this->to)
      $title = sprintf('%sdo %s ', $title, $this->to);
    return $title;
  }

}